package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.CollectivityTeachingPlanWeek;
import orm.IndividualTeachingPlanWeek;
import util.DBHelper;

public class ITeachingPlanWeekDAO {
	
	
	public void insertITPWeek(Statement statement, IndividualTeachingPlanWeek itpw) throws SQLException {
		statement.execute("insert into individualteachingplanweek values('" + itpw.getIWeekArrangeID() + "','" + itpw.getExecutionDate() + "','" +
				itpw.getExecutionWeek() + "', '" + itpw.getMonthThemeName() + "', '" + itpw.getWeekThemeName() +
				"','" + itpw.getAppropriateStudents() + "', " + itpw.getTeacherID() + ", '" + itpw.getTeacherName() + 
				"','" + itpw.getArrangeDate() + "', '" + itpw.getStarPushDate() + "', '" + itpw.getEndPushDate() +
				"','" + itpw.getEnforceTeacherName() + "')");
	}
	
	public void updateITPWeek(Statement statement, IndividualTeachingPlanWeek itpw) throws SQLException {
		statement.execute("update individualteachingplanweek set ExecutionDate = '" +
				itpw.getExecutionDate() + "', ExecutionWeek = '" + itpw.getExecutionWeek() + 
				"', MonthThemeName = '" + itpw.getMonthThemeName() + "', WeekThemeName = '" + 
				itpw.getWeekThemeName() + "', AppropriateStudents = '" + itpw.getAppropriateStudents() + "', TeacherID = " + itpw.getTeacherID() + 
				", TeacherName = '" + itpw.getTeacherName() + "', ArrangeDate = '" + itpw.getArrangeDate() + 
				"', StarPushDate = '" + itpw.getStarPushDate() + "', EndPushDate = '" + itpw.getEndPushDate() + 
				"', EnforceTeacherName = '" + itpw.getEnforceTeacherName() + "' where IWeekArrangeID = '" + itpw.getIWeekArrangeID() + "'");
	}
	
	public void deleteITPWeek(Statement statement, String iWeekArrangeID) throws SQLException {
		statement.execute("delete from individualteachingplanweek where IWeekArrangeID='" + iWeekArrangeID + "'");
	}
	
	
	public IndividualTeachingPlanWeek getitpwonthByCMAID(String iWeekArrangeID) throws SQLException {
		IndividualTeachingPlanWeek itpw = new IndividualTeachingPlanWeek();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from individualteachingplanweek where IWeekArrangeID='" + iWeekArrangeID + "'");
		
		while (rs.next()) {
			int i = 1;
			itpw.setIWeekArrangeID(rs.getString(i++));
			itpw.setExecutionDate(rs.getString(i++));
			itpw.setExecutionWeek(rs.getString(i++));
			itpw.setMonthThemeName(rs.getString(i++));
			itpw.setWeekThemeName(rs.getString(i++));
			itpw.setAppropriateStudents(rs.getString(i++));
			itpw.setTeacherID(rs.getInt(i++));
			itpw.setTeacherName(rs.getString(i++));
			itpw.setArrangeDate(rs.getString(i++));
			itpw.setStarPushDate(rs.getString(i++));
			itpw.setEndPushDate(rs.getString(i++));
			itpw.setEnforceTeacherName(rs.getString(i++));
			
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return itpw;
	}
	
	public ArrayList<IndividualTeachingPlanWeek> getAllITeachingPlanWeek(int teacherID) throws SQLException {
		ArrayList<IndividualTeachingPlanWeek> al = new ArrayList<IndividualTeachingPlanWeek>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select IWeekArrangeID,MonthThemeName,WeekThemeName, EnforceTeacherName, StarPushDate, EndPushDate from individualteachingplanweek where teacherID = " + teacherID + " ORDER BY IWeekArrangeID ASC");
		IndividualTeachingPlanWeek itpw = null;
		
		while (rs.next()) {
			itpw = new IndividualTeachingPlanWeek();
			itpw.setIWeekArrangeID(rs.getString("IWeekArrangeID"));
			itpw.setMonthThemeName(rs.getString("MonthThemeName"));
			itpw.setWeekThemeName(rs.getString("WeekThemeName"));
			itpw.setEnforceTeacherName(rs.getString("EnforceTeacherName"));
			itpw.setStarPushDate(rs.getString("StarPushDate"));
			itpw.setEndPushDate(rs.getString("EndPushDate"));
			
			al.add(itpw);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public IndividualTeachingPlanWeek getITPWNetByIWAID(String iWeekArrangeID) throws SQLException {
		IndividualTeachingPlanWeek itpweek = new IndividualTeachingPlanWeek();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select ExecutionWeek, WeekThemeName from individualteachingplanweek where IWeekArrangeID='" + iWeekArrangeID + "'");
		
		while (rs.next()) {
			int i = 1;
			itpweek.setExecutionWeek(rs.getString(i++));
			itpweek.setWeekThemeName(rs.getString(i++));
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return itpweek;
	}
	
	public ArrayList<IndividualTeachingPlanWeek> getSearchCondition(String monthThemeName,String weekThemeName,String executionDate, int teacherID) 
			throws SQLException {
		ArrayList<IndividualTeachingPlanWeek> al = new ArrayList<IndividualTeachingPlanWeek>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select IWeekArrangeID,MonthThemeName,WeekThemeName, EnforceTeacherName, StarPushDate, EndPushDate ");
		sqlStr.append("from individualteachingplanweek ");
		sqlStr.append("where TeacherID = " + teacherID + " and IWeekArrangeID != '1' ");
		
		if(monthThemeName!=null && !monthThemeName.equals("")){
			sqlStr.append(" and MonthThemeName like '%" + monthThemeName + "%'");
		}
		if(weekThemeName!=null && !weekThemeName.equals("")){
			sqlStr.append(" and WeekThemeName like '%" + weekThemeName + "%'");
		}
		if(executionDate!=null && !executionDate.equals("")){
			sqlStr.append(" and ExecutionDate = '" + executionDate + "'");
		}
		sqlStr.append(" ORDER BY IWeekArrangeID ASC ");
		
		rs = stmt.executeQuery(sqlStr.toString());

		IndividualTeachingPlanWeek itpw = null;
		
		while (rs.next()) {
			itpw = new IndividualTeachingPlanWeek();
			itpw.setIWeekArrangeID(rs.getString("IWeekArrangeID"));
			itpw.setMonthThemeName(rs.getString("MonthThemeName"));
			itpw.setWeekThemeName(rs.getString("WeekThemeName"));
			itpw.setEnforceTeacherName(rs.getString("EnforceTeacherName"));
			itpw.setStarPushDate(rs.getString("StarPushDate"));
			itpw.setEndPushDate(rs.getString("EndPushDate"));
			
			al.add(itpw);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
}
